package org.sgr.money.manager.service.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.sgr.money.manager.dto.SubjectDto;
import org.sgr.money.manager.service.SubjectService;

public class SubjectServiceImpl implements SubjectService {

	public List<SubjectDto> getAllSubject() {
		String sql = "SELECT SUB_CODE, SUB_NAME, SUB_TYPE, SUB_LEVEL, CR_DR, SUMMARY_SUB_CODE, NEGATIVE_BAL, MEMO"
				+ " FROM SUBJECT ORDER BY SUB_CODE";
		Connection conn = null;
		Statement st = null;
		List<SubjectDto> subjectDtoList = new ArrayList<SubjectDto>();
		try {
			conn = getConnection();
			st = conn.createStatement();
			ResultSet rs = st.executeQuery(sql);
			while (rs.next()) {
				SubjectDto subjectDto = getSubjectDto(rs);
				subjectDtoList.add(subjectDto);
			}
			rs.close();
		} catch (SQLException e) {
			// XXX
			e.printStackTrace();
		} finally {
			try {
				if (st != null)
					st.close();
			} catch (SQLException e) {
			}
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
			}
		}
		return subjectDtoList;
	}

	private SubjectDto getSubjectDto(ResultSet rs) throws SQLException {
		SubjectDto subjectDto = null;
		if (rs != null) {
			subjectDto = new SubjectDto();
			subjectDto.setSubCode(rs.getLong("SUB_CODE"));
			subjectDto.setSubName(rs.getString("SUB_NAME"));
			subjectDto.setSubType(rs.getString("SUB_TYPE"));
			subjectDto.setSubLevel(rs.getString("SUB_LEVEL"));
			subjectDto.setCrDr(rs.getString("CR_DR"));
			subjectDto.setSummarySubCode(rs.getLong("SUMMARY_SUB_CODE"));
			subjectDto.setNegativeBal(rs.getString("NEGATIVE_BAL"));
			subjectDto.setMemo(rs.getString("MEMO"));
		}
		return subjectDto;
	}

	private Connection getConnection() throws SQLException {
		Connection con = null;
		try {
			Class.forName("com.hxtt.sql.access.AccessDriver");
		} catch (ClassNotFoundException e) {
			System.out.println(e);
		}
		String url = "jdbc:Access:/d://data.mdb";
		String password = "";
		String username = "";
		con = DriverManager.getConnection(url, username, password);
		return con;
	}

}
